﻿Option Explicit On

Imports System.Text


Public Class Accounts

    Public Enum AccountTypes
        Individual = 1
        Corporate = 2
    End Enum
    Public Structure AccountsSummary
        Public QuoteNet As Decimal
        Public QuoteGross As Decimal
        Public InvoicedNet As Decimal
        Public InvoicedGross As Decimal
        Public Payments As Decimal
        Public Balance As Decimal
    End Structure
    Public Structure InvoiceInfo
        Public InvoiceId As Long
        Public InvoiceDate As Date
        Public DueDate As Date
        Public CompanyId As Long
        Public PersonId As Long
        Public CaseId As Long
        Public EnteredBy As String
        Public DocStoreId As Long
        'Public Cname As String
        'Public Address As String
        'Public contactname As String
    End Structure
    Public Shared Function GetInvoiceInfo(ByVal InvoiceId As Long) As InvoiceInfo
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            Dim myTab As DataTable
            Dim myInfo As New InvoiceInfo

            With SQL
                .Append("select invoice_id,invoice_date,invoice_datedue,invoice_companyid,invoice_personid,")
                .Append("invoice_caseid,invoice_enteredby, invoice_docstoreid from A_Invoices ")
                .Append("where invoice_id = " & InvoiceId)
            End With

            myTab = dataAccess.GetSQLDataTable(SQL.ToString)

            If Not IsNothing(myTab) Then
                If myTab.Rows.Count > 0 Then
                    With myInfo
                        .InvoiceId = myTab.Rows(0).Item("invoice_id")
                        .InvoiceDate = myTab.Rows(0).Item("invoice_date")
                        .DueDate = myTab.Rows(0).Item("invoice_datedue")
                        .CompanyId = myTab.Rows(0).Item("invoice_companyid")
                        .PersonId = myTab.Rows(0).Item("invoice_personid")
                        .CaseId = myTab.Rows(0).Item("invoice_caseid")
                        .EnteredBy = myTab.Rows(0).Item("invoice_enteredby")
                        .DocStoreId = myTab.Rows(0).Item("invoice_docstoreid")
                    End With

                End If
            End If

            myTab.Dispose()
            Return myInfo
        Catch ex As Exception
            Return Nothing
        Finally
            dataAccess = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function CaseSummary(ByVal AccountId As Integer, ByVal AccountType As AccountTypes) As DataTable
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL
                .Append("SELECT V_Case.Case_ID , L_Products.Product_Name, V_Case.CaseStage_Title,")
                .Append("V_Case.NetInv,V_Case.IncomeInv,V_Case.GrossInv,V_Case.Paid,V_Case.Balance ")
                .Append("FROM V_Case LEFT OUTER JOIN L_Products ON V_Case.Case_ProductID = L_Products.Product_ID ")

                Select Case AccountType
                    Case AccountTypes.Corporate
                        .Append("WHERE V_Case.Case_CompanyID = " & AccountId)
                    Case AccountTypes.Individual
                        .Append("WHERE V_Case.Case_CandidateID = " & AccountId)
                End Select

                .Append(" ORDER BY  (balance * balance)  DESC , Case_ID DESC")
            End With

            Return dataAccess.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            dataAccess = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function InvoiceSummary(ByVal AccountId As Long, ByVal AccountType As AccountTypes) As DataTable
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder


            With SQL
                .Append("SELECT A_Invoices.Invoice_ID, MAX(A_Invoices.Invoice_CaseID) AS CaseID,")
                .Append("MAX(A_Invoices.Invoice_ID) as InvNo, MAX(CONVERT(VARCHAR(10),Invoice_Date, 103)) AS InvDate,")
                .Append("MAX(CONVERT(VARCHAR(10),Invoice_DateDue, 103)) AS DueDate, CAST(SUM(A_Charges.Charge_Amount) AS money) AS Net,")
                .Append("CAST(SUM(A_Charges.Charge_Amount - A_Charges.Charge_TPCost) AS money) AS Income,")
                .Append("CAST(SUM(A_Charges.Charge_Amount + CAST(A_Charges.Charge_Amount * (A_Charges.Charge_VAT / 100) AS money)) AS money) AS Gross ")
                .Append("FROM A_Invoices INNER JOIN A_Charges ON A_Invoices.Invoice_ID = A_Charges.Charge_InvoiceID ")
                Select Case AccountType
                    Case AccountTypes.Individual
                        .Append("WHERE A_Invoices.Invoice_PersonID = " & AccountId)
                    Case AccountTypes.Corporate
                        .Append("WHERE A_Invoices.Invoice_CompanyID = " & AccountId)
                End Select
                .Append(" GROUP BY A_Invoices.Invoice_ID ")
                .Append("Order BY  MAX(A_Invoices.Invoice_CaseID) Desc,Invoice_ID Desc")
            End With

            Return dataAccess.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            dataAccess = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function QuotedCharges(ByVal CaseId As Long) As String

        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder


            With SQL
                .Append("Select Charge_ID, Charge_Type, Isnull(chargeCat_Name,'') as Category, Charge_Amount, Charge_VAT,")
                .Append("Charge_TPCost,Charge_Amount - Charge_TPCost as ChgIncome,Cast(Charge_Amount + (charge_amount *(charge_VAT/100)) as money) as ChgGross ")
                .Append("FROM A_Charges LEFT OUTER JOIN A_ChargeCategories ON A_Charges.Charge_Category = A_ChargeCategories.ChargeCat_ID ")
                .Append("WHERE Charge_CaseID = " & CaseId & " And Charge_InvoiceID = 0")
            End With

            Return SQL.ToString
        Catch ex As Exception
            Return String.Empty
        Finally
            SQL = Nothing
        End Try
    End Function
    Public Shared Function TotalQCharges(ByVal CaseId As Long) As String
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess

        Try
            Return (dataAccess.GetSQLScalar("SELECT GrossNotInv  FROM dbo.V_Case WHERE  Case_ID = " & CaseId))
        Catch ex As Exception
            Return Nothing
        Finally
            dataAccess = Nothing
        End Try
    End Function
    Public Shared Function InvoicedCharges(ByVal CaseId As Long) As String
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL
                .Append(" Select Charge_ID, Charge_InvoiceID,Charge_Type, Isnull(chargeCat_Name,'') as Category, Charge_Amount, Charge_VAT,")
                .Append(" Charge_TPCost,Charge_Amount - Charge_TPCost as ChgIncome,")
                .Append(" case CAST(dbo.A_Charges.Charge_Amount + dbo.A_Charges.Charge_Amount * (dbo.A_Charges.Charge_VAT / 100) AS money) WHEN 0 THEN charge_dummygross ELSE CAST(dbo.A_Charges.Charge_Amount + dbo.A_Charges.Charge_Amount * (dbo.A_Charges.Charge_VAT / 100) AS money) END AS ChgGross")
                .Append(" FROM A_Charges LEFT OUTER JOIN A_ChargeCategories ON A_Charges.Charge_Category = A_ChargeCategories.ChargeCat_ID ")
                .Append(" WHERE Charge_CaseID = " & CaseId & " AND Charge_InvoiceID <> 0 ")
                .Append(" ORDER By Charge_InvoiceId, Charge_ID")
            End With

            Return SQL.ToString
        Catch ex As Exception
            Return String.Empty
        Finally
        End Try
    End Function

    Public Shared Function Payments(ByVal CaseId As Long) As String

        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("Select Payment_ID, CONVERT(VARCHAR(10), Payment_dtentered, 103) as DTPaid, Payment_Type,Payment_Method, Payment_Amount,Payment_Enteredby ")
                .Append("FROM A_Payments WHERE Payment_CaseID = " & CaseId)
            End With

            Return SQL.ToString
        Catch ex As Exception
            Return String.Empty
        Finally
        End Try
    End Function
    Public Shared Function CasePaidAmt(ByVal caseid As Long) As Double
        Dim DB As New Global_Visas_Data_Layer.DataAccess
        Try
            Return DB.GetSQLScalar("SELECT   Paid  FROM  dbo.V_Case WHERE  (Case_ID = " & caseid & ")")
        Catch ex As Exception
            Return 0
        Finally
            DB = Nothing
        End Try
    End Function
    Public Shared Function ChargeTypeList(ByVal CaseId As Long) As DataTable
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            Dim ChargeGroup As Integer

            ChargeGroup = CommonDataFunctions.GblFunc_Get_Dataset(CaseId, 4)

            With SQL
                .Append("SELECT ChargeType_Id,ChargeType_Name, ChargeType_VAT, ChargeType_CategoryID, ChargeType_Amount ")
                .Append("FROM A_ChargeTypes INNER JOIN A_ChargeLinks ON A_ChargeTypes.ChargeType_ID = A_ChargeLinks.ChargeLink_ChargeTypeID ")
                .Append("WHERE A_ChargeLinks.ChargeLink_ChargeGroupID = " & ChargeGroup)
            End With

            Return dataAccess.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            dataAccess = Nothing
        End Try

    End Function

    Public Shared Function CreditTypeList() As DataTable

        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("SELECT ChargeType_Name, ChargeType_VAT, ChargeType_CategoryID")
                .Append(" FROM A_ChargeTypes INNER JOIN A_ChargeLinks ON A_ChargeTypes.ChargeType_ID = A_ChargeLinks.ChargeLink_ChargeTypeID")
                .Append(" WHERE A_ChargeLinks.ChargeLink_ChargeGroupID = 2")
            End With

            Return DB.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try

    End Function

    Public Shared Function LoadDefaultCharges(ByVal CaseId As Long) As Boolean
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim Tsk As New TasksEvents
        Dim cmn As New CommonFuncs
        Dim SQL As New StringBuilder
        Try
            SQL = New StringBuilder
      

            Dim myCase As Cases.CaseInfo = Cases.GetCaseInfo(CaseId)
            Dim DefChargeId As Integer

            SQL.Append("Select DefCharge_ID from D_DefCharges where DefCharge_ProductID = " & myCase.Productid)
            DefChargeId = db.GetSQLScalar(SQL.ToString)

            If DefChargeId = 0 Then
                CommonFuncs.DisplayMessage("No default charges found for product " & myCase.Productid & ". Please speak to an adminstrator", CommonDialog.CustomDialogIcons.Information, "Charges")
                Exit Function
            End If

            SQL = New StringBuilder

            If myCase.CompanyId = 0 Then


                With SQL
                    .Append("INSERT INTO A_Charges (Charge_Type, Charge_VAT, Charge_Amount, Charge_TPCost, Charge_DTentered, Charge_EnteredBy, Charge_CompanyID, Charge_PersonID, Charge_CaseID, Charge_Desc, Charge_Category )")
                    .Append(" SELECT DefCharge_Type, DefCharge_VAT, DefCharge_Amount, DefCharge_TPCost, '" & cmn.FormatDate(Now, True) & "' AS now, '")
                    .Append(CommonDataFunctions.GetConsultantName(GlobalCRM.CurrentConID) & "' AS Cons, " & myCase.CompanyId & " AS CoID, ")
                    .Append(myCase.CandidateId & " AS PerID, " & CaseId & " AS CaseID, 'Default Charge' AS [Desc], DefCharge_Category")
                    .Append(" FROM D_DefCharges")
                    .Append(" WHERE DefCharge_ProductID = " & myCase.Productid)
                    .Append(" AND DefCharge_CompanyID = 0")
                End With


            Else

                With SQL
                    .Append(" SELECT DefCharge_ID FROM D_DefCharges")
                    .Append(" WHERE DefCharge_ProductID = " & myCase.Productid)
                    .Append(" AND DefCharge_CompanyID = " & myCase.CompanyId)
                End With
                DefChargeId = db.GetSQLScalar(SQL.ToString)

                SQL = New StringBuilder

                With SQL
                    .Append("INSERT INTO A_Charges ( Charge_Type, Charge_VAT, Charge_Amount, Charge_TPCost, Charge_DTentered, Charge_EnteredBy, Charge_CompanyID, Charge_PersonID, Charge_CaseID, Charge_Desc, Charge_Category )")
                    .Append(" SELECT DefCharge_Type, DefCharge_VAT, DefCharge_Amount, DefCharge_TPCost, '" & cmn.FormatDate(Now, True) & "' as now, '" & GlobalCRM.CurrentConName & "' AS Cons, " & myCase.CompanyId & " AS CoID, " & myCase.CandidateId & " AS PerID, " & CaseId & " AS CaseID, 'Default Charge' AS [Desc], DefCharge_Category")
                    .Append(" FROM D_DefCharges")
                    .Append(" WHERE DefCharge_ProductID = " & myCase.Productid)
                    If DefChargeId = 0 Then
                        .Append(" AND DefCharge_CompanyID = 0")
                    Else
                        .Append(" AND DefCharge_CompanyID = " & myCase.CompanyId)
                    End If
                End With

            End If


            If db.ExecuteSQL(SQL.ToString) Then

                Dim Msg As String = "Default doc reqs loaded onto case" & vbCrLf
                Tsk.GblFunc_Add_Event("Details Changed", Msg, TasksEvents.EventStatus.SingleEvent, CaseId, 1)

                Return True

            End If
        Catch ex As Exception
        Finally
            Tsk = Nothing
            db = Nothing
            cmn = Nothing
        End Try
    End Function

    Public Shared Function HasUninvoicedCharges(ByVal CaseId As Long) As Boolean
        Dim SQL As StringBuilder
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            SQL = New StringBuilder
            With Sql
                .Append("SELECT count(*) FROM A_Charges ")
                .Append(" WHERE Charge_CaseID = " & CaseId & " AND Charge_InvoiceID = 0")
            End With

            If DB.GetSQLScalar(Sql.ToString) > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Return False
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function GetCaseBalance(ByVal CaseId As Long) As Decimal
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            Return db.GetSQLScalar("SELECT Balance FROM V_Case WHERE Case_ID = " & CaseId)
        Catch ex As Exception
            Return 0
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function CreateCharge(ByVal CaseId As Long, ByVal PersonId As Long, ByVal ChargeCatId As Integer, ByVal ChargeType As String, ByVal ConName As String, ByVal CompanyId As Long, ByVal VAT As Decimal, ByVal Net As Decimal, ByVal TPCost As Decimal, ByVal ChargeOrCredit As Integer, Optional ByVal strCurrencyChar As String = "£") As Long
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Dim cmn As New CommonFuncs
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            Dim ChargeCatId1 As Integer
            Dim objChargeCat As Object
            SQL.Append("select chargetype_categoryid from A_ChargeTypes where chargetype_id = " & ChargeCatId)
            objChargeCat = db.GetSQLScalar(SQL.ToString)
            If Not objChargeCat Is Nothing Then
                ChargeCatId1 = objChargeCat.ToString.Trim
            Else
                ChargeCatId1 = 0
            End If


            SQL = New StringBuilder
            With SQL
                .Append("Insert Into A_Charges (Charge_Type, Charge_EnteredBy, Charge_CompanyID, Charge_CaseID, Charge_PersonID, Charge_VAT, Charge_Amount, Charge_TPCost, Charge_Category)")
                .Append(" VALUES ('" & cmn.SwapQuotes(ChargeType.Trim) & "','" & cmn.SwapQuotes(ConName.Trim) & "'," & CompanyId & ",")
                .Append(CaseId & "," & PersonId & "," & VAT & "," & Net * ChargeOrCredit & "," & TPCost * ChargeOrCredit & ", " & ChargeCatId1 & ")")
            End With
            Dim id As Long = db.ExecuteSQL(SQL.ToString, True)
            If id > 0 Then
                'Dim Id As Long = db.GetIdentity

                If ChargeOrCredit = 1 Then
                    tsk.GblFunc_Add_Event("Charge Added", "Charge of type " & ChargeType & " added for " & strCurrencyChar & Net & " (Ex VAT) with TP cost of " & TPCost, 1, CaseId, TasksEvents.EventTypes.EventCase)
                Else
                    tsk.GblFunc_Add_Event("Credit Issued", "Credit issued for " & strCurrencyChar & Net & " (Ex VAT)", 1, CaseId, TasksEvents.EventTypes.EventCase)
                End If

                Return id
            End If
        Catch ex As Exception
            Return 0
        Finally
            db = Nothing
            tsk = Nothing
            cmn = Nothing
        End Try
    End Function


    Public Shared Function GetAccountStatement(ByVal Startdate As Date, ByVal EndDate As Date, ByVal Message As String, ByVal ClientName As String, Optional ByVal CompanyId As Long = 0, Optional ByVal CandidateId As Long = 0, Optional ByVal SubsidiaryList As String = "") As DataTable

        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim cmn As New CommonFuncs
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL

                .Append("SELECT MAX(CASE WHEN DT < '" & cmn.FormatDate(Startdate) & "' THEN 'Opening Balance' ELSE ID END) AS DTGroup, MAX(CASE WHEN DT < '" & cmn.FormatDate(Startdate) & "' THEN '" & Startdate & "' ELSE DT END)")
                .Append(" AS DTValue, MAX(CASE WHEN DT < '" & cmn.FormatDate(Startdate) & "' THEN 'Opening Balance' ELSE Type end) AS VType,")
                .Append(" SUM(Net) AS VNet, SUM(Gross) AS VGross, SUM(Vat) AS VVat, MAX(CASE WHEN DT < '" & cmn.FormatDate(Startdate) & "' THEN '' ELSE cast(CaseID as varchar(10)) end) AS VCaseID,")
                .Append(" MAX('" & cmn.SwapQuotes(ClientName) & "') as ClientName, MAX('" & cmn.SwapQuotes(Message) & "') as Mess, Max('" & cmn.FormatDate(EndDate, "dd/mm/yyyy") & "') as DTEnd")
                If SubsidiaryList.Length > 0 Then
                    .Append(", MAX(Company_Name) as Company")
                End If

                .Append(" FROM V_Statement")

                If SubsidiaryList.Length > 0 Then
                    .Append(" LEFT OUTER JOIN D_Company ON V_Statement.CompanyID = D_Company.Company_ID")
                End If

                If CompanyId > 0 Then
                    .Append(" WHERE CompanyID in " & SubsidiaryList)
                Else
                    .Append(" WHERE PersonID = " & CandidateId)
                End If

                .Append(" AND DT < '" & cmn.FormatDate(EndDate) & "'")
                .Append(" GROUP BY CASE WHEN DT < '" & cmn.FormatDate(Startdate) & "' THEN '" & cmn.FormatDate(Startdate) & "' ELSE DT END")
            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            cmn = Nothing
        End Try
    End Function

    Public Shared Function ResetCharges(ByVal CaseId As Long, ByVal CandidateId As Long, ByVal ProductId As Integer, ByVal ConsultantName As String, Optional ByVal CompanyId As Long = 0) As Boolean
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder


            'Reset the charges and doc reqs for new product type

            'First the charges, clear all non-invoiced charges for this case.
            With SQL
                .Append("Delete from A_Charges where Charge_CaseId = " & CaseId & " and Charge_InvoiceId = 0")
            End With

            If db.ExecuteSQL(SQL.ToString) = False Then
                Exit Function
            End If


            SQL = New StringBuilder

            'Insert relevent default charges
            If CompanyId = 0 Then
                'For private individuals use the product pricelist

                With SQL
                    .Append("INSERT INTO A_Charges ( Charge_Type, Charge_VAT, Charge_Amount, Charge_TPCost, Charge_DTentered, Charge_EnteredBy, Charge_CompanyID, Charge_PersonID, Charge_CaseID, Charge_Desc, Charge_Category )")
                    .Append(" SELECT DefCharge_Type, DefCharge_VAT, DefCharge_Amount, DefCharge_TPCost, getdate() AS now,")
                    .Append("'" & ConsultantName & "' AS Cons, 0 AS CoID, " & CandidateId & " AS PerID, " & CaseId & " AS CaseID, 'Default Charge' AS [Desc], DefCharge_Category")
                    .Append(" FROM D_DefCharges")
                    .Append(" WHERE DefCharge_ProductID = " & ProductId)
                    .Append(" AND DefCharge_CompanyID = 0")
                End With
            Else
                'Company, check for SLA price for this product

                Dim c As Integer

                With SQL
                    .Append("SELECT count(*) FROM D_DefCharges")
                    .Append(" WHERE DefCharge_ProductID = " & ProductId)
                    .Append(" AND DefCharge_CompanyID = " & CompanyId)
                End With

                c = db.GetSQLScalar(SQL.ToString)
                SQL = New StringBuilder
                With SQL
                    .Append("INSERT INTO A_Charges ( Charge_Type, Charge_VAT, Charge_Amount, Charge_TPCost, Charge_DTentered, Charge_EnteredBy, Charge_CompanyID, Charge_PersonID, Charge_CaseID, Charge_Desc, Charge_Category )")
                    .Append(" SELECT DefCharge_Type, DefCharge_VAT, DefCharge_Amount, DefCharge_TPCost, getdate() as now, '" & ConsultantName & "' AS Cons, " & CompanyId & " AS CoID, 0 AS PerID, " & CaseId & " AS CaseID, 'Default Charge' AS [Desc], DefCharge_Category")
                    .Append(" FROM D_DefCharges")
                    .Append(" WHERE DefCharge_ProductID = " & ProductId)


                    If c = 0 Then
                        .Append(" AND DefCharge_CompanyID = 0")
                    Else
                        'There is a company specific price so use that
                        .Append(" AND DefCharge_CompanyID = " & CompanyId)
                    End If
                End With
            End If

            If db.ExecuteSQL(SQL.ToString) Then
                Return True
            Else
                Return False
            End If

        Catch ex As Exception
            Return False
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function DeleteCharges(ByVal CaseId As Long, ByVal Message As String, Optional ByVal DeleteAll As Boolean = False, Optional ByVal Charges() As Long = Nothing)

        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim cmn As New CommonFuncs
        Dim tsk As New TasksEvents
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            If Not DeleteAll Then
                With SQL
                    .Append("Delete from A_Charges Where Charge_ID IN ")
                    .Append(CommonFuncs.ArrayToCSV(Charges))
                    .Append(" and Charge_CaseID = " & GlobalCRM.CurrentCaseID & " AND Charge_InvoiceID = 0")
                End With
            Else
                With SQL
                    .Append("Delete from A_Charges Where Charge_CaseID = " & GlobalCRM.CurrentCaseID & " AND Charge_InvoiceID = 0")
                End With

            End If

            If db.ExecuteSQL(SQL.ToString) Then

                Dim Msg As String

                Msg = "Quoted charges deleted, details below" & vbCrLf & vbCrLf & Message
                tsk.GblFunc_Add_Event("Charges Deleted", Msg, TasksEvents.EventStatus.SingleEvent, CaseId, TasksEvents.EventTypes.EventCase)

            End If

            Return True

        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            cmn = Nothing
            tsk = Nothing
        End Try
    End Function

    Public Shared Function CreateInvoice(ByVal CaseId As Long, ByVal CompanyId As Long, ByVal PersonId As Long, ByVal DueDate As Date, ByVal ConName As String) As Long
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim cmn As New CommonFuncs
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder
            With SQL
                .Append("Insert Into A_Invoices (Invoice_EnteredBy, Invoice_Date, Invoice_CompanyID, Invoice_PersonID, Invoice_CaseID, Invoice_Datedue)")
                .Append(" VALUES ('" & cmn.SwapQuotes(ConName) & "','" & cmn.FormatDate(Now, True) & "', " & CompanyId & " , " & PersonId & "," & CaseId & ", ' " & cmn.FormatDate(DueDate) & " ')")
            End With
            Return db.ExecuteSQL(SQL.ToString, True)
            
        Catch ex As Exception
            Return 0
        Finally
            db = Nothing
            cmn = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function GetInvoiceDueDate(ByVal CompanyId As Long) As Date
        Dim db As New Global_Visas_Data_Layer.DataAccess

        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            Dim DaysCredit As Integer

            With SQL
                .Append(" SELECT ISNULL(L_AccountTypes.AccountType_DaysCredit, 0) AS DC")
                .Append(" FROM D_Company LEFT OUTER JOIN L_AccountTypes ON D_Company.Company_AccountType = L_AccountTypes.AccountType_ID")
                .Append(" WHERE D_Company.Company_ID = " & CompanyId)
            End With

            DaysCredit = db.GetSQLScalar(SQL.ToString)

            Return Now.AddDays(DaysCredit)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function UpdateChargesAsInvoiced(ByVal CaseId As Long, ByVal InvoiceId As Long, Optional ByVal Charges() As Long = Nothing, Optional ByVal InvoiceAll As Boolean = False) As Boolean

        Dim db As New Global_Visas_Data_Layer.DataAccess

        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("UPDATE A_Charges SET Charge_InvoiceID = " & InvoiceId)
                .Append(" where charge_caseid = " & CaseId & " AND Charge_InvoiceID = 0")

                If Not InvoiceAll Then
                    .Append(" and charge_id in " & CommonFuncs.ArrayToCSV(Charges))
                End If

            End With

            Return db.ExecuteSQL(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing

        End Try
    End Function

    Public Shared Function Gblfunc_Print_Invoice(ByVal InvId As Long, ByVal IntPrint As Integer) As Long

        'IntPrint 0/1 = Create only, 2 = print, 3 = E-Mail, 4 = H: drive
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim sql As StringBuilder
        Try

            Dim myInvoice As Accounts.InvoiceInfo = Accounts.GetInvoiceInfo(InvId)
            Dim DocStoreId As Long = 0
            Dim InvoiceName As String = String.Empty
            Dim strCurrChar As String = CStr(CommonDataFunctions.GetCurrencyCodeCharacter(GlobalCRM.CurrentCaseID))
            Dim strTaxDesc As String = CommonDataFunctions.GetGovTax(GlobalCRM.CurrentCaseID)
            Dim strBankDetails As String = Branch.GetBranchInvoiceDetails(GlobalCRM.CurrentCaseID)
            Dim objDT As DataTable
            Dim objDR As DataRow
            Dim strFilename As String = String.Empty

            Dim strItems As String = String.Empty
            Dim strNET As String = String.Empty
            Dim strTAX As String = String.Empty
            Dim strTotal As String = String.Empty
            Dim dblTotalTax As Double
            Dim intPtr As Integer = 0
            Dim dblTotal As Double = 0
            Dim dblTotalNet As Double = 0
            Dim strTitle As String = GetInvoiceTitle(GlobalCRM.CurrentCaseID)

            Dim strCName As String
            Dim strAddressTo As String = String.Empty
            Dim strPostCode As String = String.Empty
            Dim strAttnName As String = String.Empty

            Dim objWORD As Microsoft.Office.Interop.Word.Application = Nothing
            Dim objDOc As Microsoft.Office.Interop.Word.Document = Nothing
            Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader
            Dim DbServerName As String = CType(configurationAppSettings.GetValue("DocStoreServer", GetType(System.String)), String)
            Try

                If myInvoice.InvoiceId = 0 Then
                    CommonFuncs.DisplayMessage("Invoice number " & InvId & " not found.", CommonDialog.CustomDialogIcons.Information, "Error")
                    Exit Function
                End If

                objWORD = New Microsoft.Office.Interop.Word.Application
                Select Case IntPrint
                    Case 1
                        'Print
                        objDOc = objWORD.Documents.Open("\\" & DbServerName.Trim & "\Company\TemplatesLetters\CommonTemplates\Invoice1.dot")
                    Case 2
                        'E_Mail. 
                        objDOc = objWORD.Documents.Open("\\" & DbServerName.Trim & "\Company\TemplatesLetters\CommonTemplates\Invoice_Template1.dot")

                    Case 3
                        'H: drive. Save copy to H: drive
                        If CommonFuncs.DisplayyesNoMessage("Would you like the Invoice to be saved in the Global Visas template?", CommonDialog.CustomDialogIcons.Question, "Invoice") = 1 Then
                            objDOc = objWORD.Documents.Open("\\" & DbServerName.Trim & "\Company\TemplatesLetters\CommonTemplates\Invoice_Template1.dot")
                        Else
                            objDOc = objWORD.Documents.Open("\\" & DbServerName.Trim & "\Company\TemplatesLetters\CommonTemplates\Invoice1.dot")
                        End If
                    Case Else
                        'No output
                        objDOc = objWORD.Documents.Open("\\" & DbServerName.Trim & "\Company\TemplatesLetters\CommonTemplates\Invoice_Template1.dot")
                End Select
                'objDOc = objWORD.Documents.Open("\\Constellation\Company\TemplatesLetters\CommonTemplates\Invoice.dot")
                'objWORD.Visible = True

                With myInvoice
                    SetWordBmk(objDOc, "INVOICEID", .InvoiceId)
                    SetWordBmk(objDOc, "InvoiceDate", .InvoiceDate.ToString("dd/MM/yyyy"))
                    SetWordBmk(objDOc, "DueDate", .DueDate.ToString("dd/MM/yyyy"))
                    SetWordBmk(objDOc, "CaseID", .CaseId)
                    Dim dtAddr As DataTable
                    Dim strsql As StringBuilder
                    If .CompanyId > 0 Then

                        'company case - address to the company, attn to contact
                        strCName = db.GetSQLScalar("select company_name from d_company where company_id = " & .CompanyId).ToString
                        strsql = New StringBuilder
                        With strsql
                            .Append("SELECT     dbo.D_Address.Address_Text, dbo.D_Address.Address_Postcode, dbo.D_Address.Address_Country, dbo.D_Address.Address_Region, ")
                            .Append(" dbo.D_AddressLink.AddressLink_Type")
                            .Append(" FROM         dbo.D_Company INNER JOIN")
                            .Append(" dbo.D_AddressLink ON dbo.D_Company.Company_ID = dbo.D_AddressLink.AddressLink_CompanyID INNER JOIN")
                            .Append(" dbo.D_Address ON dbo.D_AddressLink.AddressLink_AddressID = dbo.D_Address.Address_ID")
                            .Append(" WHERE(dbo.D_Company.Company_ID = " & myInvoice.CompanyId & ")AND (dbo.D_AddressLink.AddressLink_Type >= 2)")
                        End With
                        dtAddr = db.GetSQLDataTable(strsql.ToString)
                        If Not dtAddr Is Nothing Then
                            If dtAddr.Rows.Count > 0 Then
                                strAddressTo = IIf(IsDBNull(dtAddr.Rows(0).Item("Address_Text")), Nothing, dtAddr.Rows(0).Item("Address_Text"))
                                strPostCode = IIf(IsDBNull(dtAddr.Rows(0).Item("Address_Postcode")), Nothing, dtAddr.Rows(0).Item("Address_Postcode"))
                            End If
                        End If
                        Dim objName As Object

                        strsql = New StringBuilder
                        With strsql
                            .Append("SELECT     dbo.D_Person.Person_FName + ' ' + dbo.D_Person.Person_LName AS CName")
                            .Append(" FROM         dbo.D_Case INNER JOIN")
                            .Append(" dbo.D_Person ON dbo.D_Case.Case_ContactID = dbo.D_Person.Person_ID")
                            .Append(" WHERE(dbo.D_Case.Case_ID = " & myInvoice.CaseId & ")")
                            objName = db.GetSQLScalar(.ToString)
                        End With

                        If Not objName Is Nothing Then
                            strAttnName = objName.ToString
                        Else
                            strAttnName = String.Empty
                        End If
                        SetWordBmk(objDOc, "CNAME", strCName)
                        SetWordBmk(objDOc, "ADDRESS2", strAddressTo)
                        SetWordBmk(objDOc, "ADDRESS1", strPostCode)
                        SetWordBmk(objDOc, "CONTACTNAME", strAttnName)

                    Else
                        strsql = New StringBuilder
                        With strsql
                            .Append("SELECT     dbo.D_Person.Person_FName + ' ' + dbo.D_Person.Person_LName AS CName")
                            .Append(" FROM         dbo.D_Case INNER JOIN")
                            .Append(" dbo.D_Person ON dbo.D_Case.Case_CandidateID = dbo.D_Person.Person_ID")
                            .Append(" WHERE(dbo.D_Case.Case_ID = " & myInvoice.CaseId & ")")
                            strCName = db.GetSQLScalar(.ToString).ToString
                        End With

                        strsql = New StringBuilder
                        With strsql
                            .Append(" SELECT     dbo.D_Address.Address_Text, dbo.D_Address.Address_Postcode, dbo.D_Address.Address_Country, dbo.D_Address.Address_Region, ")
                            .Append("                         dbo.D_Person.Person_ID")
                            .Append(" FROM         dbo.D_Address INNER JOIN")
                            .Append("  dbo.D_AddressLink ON dbo.D_Address.Address_ID = dbo.D_AddressLink.AddressLink_AddressID INNER JOIN")
                            .Append(" dbo.D_Person ON dbo.D_AddressLink.AddressLink_PersonID = dbo.D_Person.Person_ID")
                            .Append(" WHERE     (dbo.D_AddressLink.AddressLink_Type >= 2) AND (dbo.D_Person.Person_ID = " & myInvoice.PersonId & ")")

                            dtAddr = db.GetSQLDataTable(strsql.ToString)
                            If Not dtAddr Is Nothing Then
                                If dtAddr.Rows.Count > 0 Then
                                    strAddressTo = IIf(IsDBNull(dtAddr.Rows(0).Item("Address_Text")), Nothing, dtAddr.Rows(0).Item("Address_Text"))
                                    strPostCode = IIf(IsDBNull(dtAddr.Rows(0).Item("Address_Postcode")), Nothing, dtAddr.Rows(0).Item("Address_Postcode"))
                                End If
                            End If

                            SetWordBmk(objDOc, "CNAME", strCName)
                            SetWordBmk(objDOc, "ADDRESS2", strAddressTo)
                            SetWordBmk(objDOc, "ADDRESS1", strPostCode)
                            SetWordBmk(objDOc, "CONTACTNAME", strCName)
                        End With
                    End If
                    Sql = New StringBuilder
                    Sql.Append("Select * from A_Charges where Charge_INVOICEID = " & CStr(.InvoiceId))
                    objDT = db.GetSQLDataTable(Sql.ToString)

                    If Not objDT Is Nothing Then
                        If objDT.Rows.Count > 0 Then
                            For Each objDR In objDT.Rows
                                strItems = strItems & objDR("Charge_Type") & vbCrLf
                                strNET &= strCurrChar & Format(objDR("Charge_Amount"), "0.00") & vbCrLf
                                dblTotalNet += objDR("Charge_Amount")
                                strTAX &= strCurrChar & Format((objDR("Charge_Amount") * (objDR("Charge_VAT") / 100)), "0.00") & vbCrLf
                                dblTotalTax += (objDR("Charge_Amount") * (objDR("Charge_VAT") / 100))
                                strTotal &= strCurrChar & Format(((objDR("Charge_Amount") + (objDR("Charge_Amount")) * (objDR("Charge_VAT") / 100))), "0.00") & vbCrLf
                                dblTotal += objDR("Charge_Amount") + (objDR("Charge_Amount") * (objDR("Charge_VAT") / 100))
                            Next
                        End If
                    End If



                    SetWordBmk(objDOc, "ITEMS", strItems)
                    SetWordBmk(objDOc, "NET", strNET)
                    SetWordBmk(objDOc, "TAX", strTAX)
                    SetWordBmk(objDOc, "Total", strTotal)
                    SetWordBmk(objDOc, "TotalTax", strCurrChar & Format(dblTotalTax, "0.00"))
                    SetWordBmk(objDOc, "TOTALTOTAL", strCurrChar & Format((dblTotal), "0.00"))
                    SetWordBmk(objDOc, "TotalNet", strCurrChar & CStr(Format(dblTotalNet, "0.00")))
                    SetWordBmk(objDOc, "AmountDue", strCurrChar & CStr(Format(dblTotal, "0.00")))
                    SetWordBmk(objDOc, "TaxDesc", strTaxDesc)
                    SetWordBmk(objDOc, "CaseDesc", strTitle)
                    SetWordBmk(objDOc, "BankDetails", strBankDetails)

                    'objWORD.Visible = True

                End With


                ' DocStoreId = Letters.GblFunc_Create_DocStore("Invoice", "Invoice", myInvoice.CaseId)
                '  InvoiceName = Letters.Gblfunc_Get_DocStorePath(DocStoreId) & "\Invoice_" & InvId & ".doc"



                Gblfunc_Print_Invoice = DocStoreId


                'Letters.GblFunc_Add_Doc(InvoiceName, "Invoice", DocStoreId)
                '*****************************************
                Select Case IntPrint

                    Case 1
                        'Print
                        objDOc.PrintOut()
                        'objDOc.SaveAs(InvoiceName)
                        strFilename = "C:\Database\Invoice_" & InvId & ".doc"
                        objDOc.SaveAs(strFilename)
                        Letters.GblFunc_Add_FileToStore(strFilename, "Invoice", DocStoreId)
                    Case 2
                        'E_Mail. Copy to local drive, put value in attachments table and open E-Mail form.
                        strFilename = "C:\Database\Invoice_" & InvId & ".doc"
                        objDOc.SaveAs(strFilename)
                        Letters.GblFunc_Add_FileToStore(strFilename, "Invoice", DocStoreId)
                        'Letters.GblFunc_Add_FileToStore(InvoiceName, "Invoice", DocStoreId)
                        GlobalCRM.LocalAttachments.AddAttachment("Invoice for case no. " & GlobalCRM.CurrentCaseID, strFilename)
                        'frmFinances.Dispose()
                        'With frmsendemail
                        '    .InitialiseForm(GlobalCRM.CurrentCaseID, frmSendEmail.DataTypes.VisaCase, frmSendEmail.EmailType.Candidate)
                        '    .MdiParent = frmMDIMain
                        '    .Show()
                        'End With

                    Case 3
                        'H: drive. Save copy to H: drive

                        strFilename = "H:\Invoice_" & InvId & ".doc"
                        Try
                            If IO.File.Exists(strFilename) = True Then
                                Kill(strFilename)
                            End If
                            objDOc.SaveAs(strFilename)
                            'objDOc.SaveAs(InvoiceName)
                            'Letters.GblFunc_Add_FileToStore(InvoiceName, "Invoice", DocStoreId)
                            'Letters.GblFunc_Add_FileToStore(strFilename, "Invoice", DocStoreId)
                        Catch exc As Exception
                            CommonFuncs.DisplayMessage("There was an error trying to save the document to the H: drive.", CommonDialog.CustomDialogIcons.Information, "Error")
                            Exit Function
                        End Try
                    Case Else
                        'No output

                End Select
                '*****************************************
                Return DocStoreId


                Exit Function




                'Dim CrystRpt As New CrystalDecisions.CrystalReports.Engine.ReportDocument
                'Dim Rpt As New InvoiceReport
                'Rpt.SetDataSource(ReportCRM.GetReportData(ReportCRM.ReportType.InvoiceReport))
                'Rpt.SetParameterValue("CurrencyValue", CStr(CommonDataFunctions.GetCurrencyCodeCharacter(GlobalCRM.CurrentCaseID)))

                'CrystRpt = Rpt

                Letters.GblFunc_Add_Doc(InvoiceName, "Invoice", DocStoreId)
                '*****************************************
                Select Case IntPrint

                    Case 1
                        'Print

                        strFilename = "C:\Database\Invoice_CaseID_" & GlobalCRM.CurrentCaseID & ".doc"

                        'CrystRpt.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.WordForWindows, strFilename)
                        'CrystRpt.PrintToPrinter(1, False, 0, 0)
                        Kill(strFilename)


                    Case 2
                        'E_Mail. Copy to local drive, put value in attachments table and open E-Mail form.
                        strFilename = "C:\Database\Invoice_" & InvId & ".doc"
                        Letters.GblFunc_Add_FileToStore(strFilename, "Invoice", DocStoreId)
                        GlobalCRM.LocalAttachments.AddAttachment("Invoice for case no. " & GlobalCRM.CurrentCaseID, strFilename)
                        'CrystRpt.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.WordForWindows, strFilename)
                        'frmFinances.Dispose()
                        'With frmSendEmail
                        '                        .InitialiseForm(GlobalCRM.CurrentCaseID, frmSendEmail.DataTypes.VisaCase, frmSendEmail.EmailType.Candidate)
                        '.MdiParent = frmMDIMain
                        '.Show()
                        'End With

                    Case 3
                        'H: drive. Save copy to H: drive

                        strFilename = "H:\Invoice_" & InvId & ".doc"
                        'CrystRpt.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.WordForWindows, strFilename)
                    Case Else
                        'No output
                        'CrystRpt.Close()

                End Select
                '*****************************************
                Return DocStoreId

            Catch ex As Exception
                'CommonFunc.ErrorHandler(ex, "Accounts.gblFunc_Print_Invoice")
            Finally
                If Not objDOc Is Nothing Then
                    objDOc.Close(False)
                    objWORD.Quit(False)
                End If
            End Try
        Catch ex1 As Exception
        Finally
            db = Nothing
        End Try
    End Function

    Private Shared Sub SetWordBmk(ByVal objDoc As Microsoft.Office.Interop.Word.Document, ByVal strBMK As String, ByVal objValue As Object)
        Try
            Dim objRNG As Microsoft.Office.Interop.Word.Range
            objRNG = objDoc.Bookmarks(strBMK).Range
            objRNG.Text = CStr(objValue)
        Catch ex As Exception

        End Try
    End Sub

    Public Shared Function GetInvoiceTitle(ByVal lngCaseID As Long) As String
        Dim DB As New Global_Visas_Data_Layer.DataAccess

        Try
            Dim strSQL As String

            strSQL = "SELECT     dbo.L_Products.Product_Name + ' for ' + dbo.D_Person.Person_FName + ' ' + dbo.D_Person.Person_LName AS CaseTitle " & _
                        "FROM dbo.D_Case INNER JOIN " & _
                        "dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID INNER JOIN " & _
                        "dbo.D_Person ON dbo.D_Case.Case_CandidateID = dbo.D_Person.Person_ID " & _
                        "WHERE(dbo.D_Case.Case_ID = " & CStr(lngCaseID) & ")"

            Return DB.GetSQLScalar(strSQL)
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
        End Try
    End Function

    Public Shared Function GetChargeTypes() As DataTable

        Dim DB As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As New StringBuilder
        Try

            SQL = New StringBuilder

            With SQL
                .Append("SELECT A_ChargeTypes.ChargeType_ID, A_ChargeTypes.ChargeType_Name, A_ChargeTypes.ChargeType_VAT, A_ChargeCategories.ChargeCat_Name")
                .Append(" FROM A_ChargeTypes LEFT OUTER JOIN A_ChargeCategories ON A_ChargeTypes.ChargeType_CategoryID = A_ChargeCategories.ChargeCat_ID")
                .Append(" WHERE ChargeType_CategoryID not in (4,5,6)")
                .Append(" Order By ChargeType_Name")
            End With

            Return DB.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally

            DB = Nothing
        End Try
    End Function

    Public Shared Function GetChargeGroups() As DataTable

        Dim DB As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As New StringBuilder
        Try

            SQL = New StringBuilder

            With SQL
                .Append("Select ChargeGroup_ID, chargeGroup_Name")
                .Append(" FROM A_ChargeGroups WHERE ChargeGroup_system = 0")
                .Append(" Order By ChargeGroup_Name")
            End With

            Return DB.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally

            DB = Nothing
        End Try
    End Function

    Public Shared Function GetChargeLinks(ByVal ChargeGroupId As Integer) As DataTable

        Dim DB As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As New StringBuilder
        Try

            SQL = New StringBuilder

            With SQL
                .Append("SELECT A_ChargeLinks.ChargeLink_ID, A_ChargeTypes.ChargeType_Name ")
                .Append(" FROM A_ChargeLinks INNER JOIN")
                .Append(" A_ChargeTypes ON A_ChargeLinks.ChargeLink_ChargeTypeID = A_ChargeTypes.ChargeType_ID")
                .Append(" WHERE A_ChargeLinks.ChargeLink_ChargeGroupID = " & ChargeGroupId)
                .Append(" ORDER By ChargeType_Name")
            End With

            Return DB.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally

            DB = Nothing
        End Try
    End Function

    Public Shared Function GetChargeLinkId(ByVal GroupId As Integer, ByVal TypeId As Integer) As Long
        Dim DB As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As New StringBuilder
        Try

            SQL = New StringBuilder

            With SQL
                .Append("Select ChargeLink_Id from A_ChargeLinks ")
                .Append(" WHERE ChargeLink_ChargeGroupID = " & GroupId)
                .Append(" AND ChargeLink_ChargeTypeID = " & TypeId)
            End With

            Return DB.GetSQLScalar(SQL.ToString)
        Catch ex As Exception
            Return 0
        Finally

            DB = Nothing
        End Try
    End Function

    Public Shared Function GetChargeLinkTypeId(ByVal LinkId As Long) As Integer
        Dim DB As New Global_Visas_Data_Layer.DataAccess
        Try

            Return DB.GetSQLScalar("Select ChargeLink_ChargeTypeID from A_ChargeLinks where ChargeLink_ID = " & LinkId)
        Catch ex As Exception
            Return 0
        Finally

            DB = Nothing
        End Try
    End Function

    Public Shared Function DeleteChargeLink(ByVal LinkId As Long, ByVal TypeId As Integer, ByVal TypeName As String, ByVal GroupId As Integer, ByVal GroupName As String) As Boolean
        Dim DB As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents

        Try
            If DB.ExecuteSQL("Delete from A_ChargeLinks where ChargeLink_ID = " & LinkId) Then

                Dim Msg As String

                Msg = "Charge Type no. " & TypeId & " (" & TypeName & ") removed from Charge Group no. " & GroupId & "(" & GroupName & ")"

                tsk.GblFunc_Add_SysEvent("Admin (Charge Types)", Msg)

                Return True

            End If
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
            tsk = Nothing

        End Try
    End Function

    Public Shared Function CreateChargeLink(ByVal TypeId As Integer, ByVal GroupId As Integer, ByVal ChargeTypeName As String, ByVal ChargeGroupName As String) As Long
        Dim DB As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Dim cmn As New CommonFuncs
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            Dim LinkId As Long

            With SQL
                .Append("Insert into A_ChargeLinks (ChargeLink_ChargeTypeID, ChargeLink_ChargeGroupID)")
                .Append(" VALUES(" & TypeId & "," & GroupId & ")")
            End With
            LinkId = DB.ExecuteSQL(SQL.ToString, True)
            If LinkId > 0 Then

                If LinkId > 0 Then

                    Dim Msg As String

                    Msg = "Charge Type no. " & TypeId & " (" & cmn.SwapQuotes(ChargeTypeName) & ") added to group " & GroupId & " (" & cmn.SwapQuotes(ChargeGroupName) & ")"
                    tsk.GblFunc_Add_SysEvent("Admin (Charge Types)", Msg)
                End If
            End If

            Return LinkId
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
            tsk = Nothing
            cmn = Nothing
        End Try
    End Function

    Public Shared Function AnyPayments(ByVal CaseId As Long) As Boolean
        Dim DB As New Global_Visas_Data_Layer.DataAccess

        Try
            Dim c As Long

            c = DB.GetSQLScalar("Select count(*) from A_Payments where Payment_CaseId = " & CaseId)

            If c > 0 Then
                Return True
            End If
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
        End Try
    End Function

    Public Shared Function AnyInvoices(ByVal CaseId As Long) As Boolean
        Dim DB As New Global_Visas_Data_Layer.DataAccess

        Try
            Dim c As Long
            c = DB.GetSQLScalar("Select count(*) from A_Invoices where Invoice_CaseId = " & CaseId)

            If c > 0 Then
                Return True
            End If
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
        End Try
    End Function

    Public Shared Function GetPaymentTypes(ByVal PaymentTypeGroup As Integer) As DataTable

        Dim DB As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder


            With SQL
                .Append("Select PaymentType_ID, PaymentType_Name From A_PaymentTypes Where PaymentType_Group = " & PaymentTypeGroup & " Order by PaymentType_Name")
            End With

            Return DB.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function GetPaymentMethods() As DataTable

        Dim DB As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder

            With SQL
                .Append("Select PaymentMethod_ID, PaymentMethod_Name From A_PaymentMethods Order by PaymentMethod_Name")
            End With

            Return DB.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function CreatePayment(ByVal CaseId As Long, ByVal PersonID As Long, ByVal CompanyID As Long, ByVal ConsultantName As String, ByVal PaymentMethod As String, ByVal PaymentType As String, ByVal Amount As Decimal, ByVal PaymentMode As Integer) As Boolean
        Dim DB As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder

            With SQL
                .Append("Insert Into A_payments (Payment_CaseID,Payment_Method, Payment_EnteredBy,Payment_PersonID, Payment_CompanyID,Payment_Type, Payment_Amount)")
                If GlobalCRM.CurrentCompanyID = 0 Then
                    .Append(" VALUES (" & CaseId & ",'" & PaymentMethod & "','" & ConsultantName & "'," & PersonID & "," & 0 & ", '" & PaymentType & "', " & Amount * PaymentMode & ")")
                Else
                    .Append(" VALUES (" & CaseId & ",'" & PaymentMethod & "','" & ConsultantName & "'," & 0 & "," & CompanyID & ", '" & PaymentType & "', " & Amount * PaymentMode & ")")

                End If
            End With

            If DB.ExecuteSQL(SQL.ToString) Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Return False
        Finally
            DB = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function GetAccountsSummary(ByVal CaseID As Long) As AccountsSummary

        Dim SQL As New StringBuilder
        Dim myData As DataTable
        Dim mySummary As New AccountsSummary
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        GetAccountsSummary = Nothing

        If CaseID > 0 Then

            'Get quote amounts (charges that have not been invoiced)
            With SQL
                .Append("select coalesce(sum(charge_amount),0) as quotenet, coalesce(sum(charge_amount +( charge_amount * (charge_vat/100))),0) as quotegross from a_charges ")
                .Append("where charge_caseid = " & CaseID & " and charge_invoiceid = 0")
                myData = DB.GetSQLDataTable(.ToString)
            End With

            If Not IsNothing(myData) Then
                If myData.Rows.Count > 0 Then
                    mySummary.QuoteNet = myData.Rows(0).Item("quotenet")
                    mySummary.QuoteGross = myData.Rows(0).Item("quotegross")
                End If
            End If

            myData.Dispose()

            SQL = New StringBuilder

            With SQL
                .Append("select coalesce(sum(charge_amount),0) as invoicenet, coalesce(sum(charge_amount +( charge_amount * (charge_vat/100))),0) as invoicegross from a_charges ")
                .Append("where charge_caseid = " & CaseID & " and charge_invoiceid > 0")
                myData = DB.GetSQLDataTable(.ToString)
            End With

            If Not IsNothing(myData) Then
                If myData.Rows.Count > 0 Then
                    mySummary.InvoicedNet = myData.Rows(0).Item("invoicenet")
                    mySummary.InvoicedGross = myData.Rows(0).Item("invoicegross")
                End If
            End If

            SQL = New StringBuilder

            With SQL
                .Append("select coalesce(sum(payment_amount),0) from a_payments where payment_caseid = " & CaseID)
                mySummary.Payments = DB.GetSQLScalar(.ToString)
            End With

            myData.Dispose()

            mySummary.Balance = mySummary.InvoicedGross - mySummary.Payments

            Return mySummary
        End If

    End Function
    Public Shared Function CreditChargesAccess(ByVal Con_id As Long) As Long
        Dim DB As New Global_Visas_Data_Layer.DataAccess
        Try
            Dim sData As Object = DB.GetSQLScalar("select con_CreditCharges from l_consultant where con_id = " & Con_id)
            If Not sData Is Nothing Then
                If sData.GetType.ToString = "System.DBNull" Then
                    Return 0
                Else
                    Return CLng(sData)
                End If
            Else
                Return 0
            End If
        Catch ex As Exception
            Return 0
        Finally
            DB = Nothing
        End Try
    End Function
    Public Shared Function IssueRefundAccess(ByVal Con_id As Long) As Long
        Dim DB As New Global_Visas_Data_Layer.DataAccess
        Try
            Dim sData As Object = DB.GetSQLScalar("select con_IssueRefund from l_consultant where con_id = " & Con_id)
            If Not sData Is Nothing Then
                If sData.GetType.ToString = "System.DBNull" Then
                    Return 0
                Else
                    Return CLng(sData)
                End If
            Else
                Return 0
            End If
        Catch ex As Exception
            Return 0
        Finally
            DB = Nothing
        End Try
    End Function
    Public Shared Function GetInvoiceDetails(ByVal invid As Long) As DataTable

        Dim DB As New Global_Visas_Data_Layer.DataAccess
        Try
            Return DB.GetSQLDataTable("select  invoice_date, invoice_datedue,Invoice_enteredby from a_invoices where invoice_id = " & invid)
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
        End Try
    End Function
    Public Shared Function GetPaymentDetails(ByVal invid As Long) As DataTable

        Dim DB As New Global_Visas_Data_Layer.DataAccess
        Try
            Return DB.GetSQLDataTable("select  invoice_date, invoice_datedue,Invoice_enteredby from a_invoices where invoice_id = " & invid)
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
        End Try
    End Function
End Class
